package com.sina;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;


public class DB {
	private String url="jdbc:mysql://127.0.0.1:3307/sina";
	private String username="root";
	private String password="dereky";
	private String classname="com.mysql.jdbc.Driver";
	private Connection con;
	private PreparedStatement pstmt;
	
	public DB(){
		try {
			Class.forName(classname);
			con=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void execute(String sql,Object [] params) {
		try {
			if(sql!=null&&!sql.equals("")){
				if(params==null){
					params=new Object[0];
				}
				if(con!=null){
					pstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					for(int i=0;i<params.length&&params[i]!=null;i++){
						//System.out.println("i:"+i+":"+params[i]);
						pstmt.setObject(i+1, params[i]);
					}
					pstmt.execute();
				}
			}			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
    public ResultSet getResultset(){
    	try {
    		return pstmt.getResultSet();
		} catch (Exception e) {
			// TODO: handle exception
			return null;
		}
    }	
     public void close() {
    	try {
        	 if(pstmt!=null){
        		 pstmt.close();
        	 }
        	 if(con!=null){
        		 con.close();
        	 }			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
    public static void main(String []args) throws SQLException{
    	test2(1);
    	System.out.println("DB.main()");
    }
    public static void test1() throws SQLException{
    	DB db=new DB();
    	String sql="select * from username";
    	db.execute(sql, null);
    	ResultSet rs=db.getResultset();
    	while(rs.next()){
    		System.out.print("username  "+rs.getString("username") + "\n");
    	}
		db.close();
    	
    }
    public static int cnt = 1;
    public static void test2(int x){
    	Random random = new Random();
    	String sql1 = "insert into username(username,pwd) values(\""+ (cnt++) + 
    			"_"+ random.nextInt(10) + "\",\"" + "aaa" + "_"+ random.nextInt(100) +"\")";
    	String sql2 = "insert into username(username,pwd) values(\""+ (cnt++) + 
    			"_"+ random.nextInt(10) + "\",\"" + "bbb" + "_"+ random.nextInt(100) +"\")";
    	System.out.println(sql1+"\n"+sql2);
    	DB db=new DB();
    	try {
			db.con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
			db.con.setAutoCommit(false);
			Statement st = db.con.createStatement();
			st.executeUpdate(sql1);
			try {
				Thread.sleep(1000);
				if(x==2){
					throw new SQLException("thread timeout");
				}
				
			} catch (InterruptedException e) {
				System.out.println("thread wait");
				e.printStackTrace();
				throw new SQLException("thread timeout2");
			}
			st.executeUpdate(sql2);
			db.con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				db.con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				db.con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    	
    	
    }
}
